package com.struts.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Statement;
import com.struts.dao.UserDao;
import com.struts.pojo.User;
import com.struts.utils.DBUtils;

public class UserDaoImpl implements UserDao{

	public List<User> getAllUser() {
		Connection connection = DBUtils.getConnection() ;
		StringBuffer buffer = new StringBuffer() ;
		buffer.append("select * from s_user") ;
		List<User> userList = new ArrayList<User>() ;
		try {
			Statement pstmt = (Statement) connection.createStatement() ;
			ResultSet rs = pstmt.executeQuery(buffer.toString()) ;
			while(rs.next()) {
				User user = new User() ;
				user.setUserID(rs.getInt("userID")) ;
				user.setUsername(rs.getString("username")) ;
				user.setPassword(rs.getString("password")) ;
				user.setSex(rs.getString("sex")) ;
				user.setBirthday(rs.getString("birthday")) ;
				user.setEducation(rs.getString("education")) ;
				user.setTelephone(rs.getString("telephone")) ;
				user.setInterest(rs.getString("interest")) ;
				user.setPath(rs.getString("path")) ;
				user.setFilename(rs.getString("filename")) ;
				user.setRemark(rs.getString("remark")) ;
				userList.add(user) ;
			}
			return userList ;
		} catch (Exception e) {
			e.printStackTrace() ;
		}
		return null ;
	}

	public User getUserById(Serializable id){
		Connection connection = DBUtils.getConnection() ;
		StringBuffer buffer = new StringBuffer() ;
		buffer.append("select * from s_user where userID=" + id) ;
		Statement pstmt = null ;
		ResultSet rs = null ;
		User user = new User() ;
		try {
			pstmt = (Statement) connection.createStatement() ;
			rs = pstmt.executeQuery(buffer.toString()) ;
			if(rs.next()) {
				user.setUserID(rs.getInt("userID")) ;
				user.setUsername(rs.getString("username")) ;
				user.setPassword(rs.getString("password")) ;
				user.setSex(rs.getString("sex")) ;
				user.setBirthday(rs.getString("birthday")) ;
				user.setEducation(rs.getString("education")) ;
				user.setTelephone(rs.getString("telephone")) ;
				user.setInterest(rs.getString("interest")) ;
				user.setPath(rs.getString("path")) ;
				user.setFilename(rs.getString("filename")) ;
				user.setRemark(rs.getString("remark")) ;
				return user;
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		}
		return null ;
	}

	public void saveUser(User user) {
		Connection connection = DBUtils.getConnection();
		Statement pstmt = null ;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("insert into s_user (username,password,sex,birthday,education,telephone,interest,path,filename,remark) values");
		stringBuffer.append("('");
		stringBuffer.append(user.getUsername());
		stringBuffer.append("','");
		stringBuffer.append(user.getPassword());
		stringBuffer.append("','");
		stringBuffer.append(user.getSex());
		stringBuffer.append("','");
		stringBuffer.append(user.getBirthday());
		stringBuffer.append("','");
		stringBuffer.append(user.getEducation());
		stringBuffer.append("','");
		stringBuffer.append(user.getTelephone());
		stringBuffer.append("','");
		stringBuffer.append(user.getInterest()) ;
		stringBuffer.append("','");
		stringBuffer.append(user.getPath());
		stringBuffer.append("','");
		stringBuffer.append(user.getFilename());
		stringBuffer.append("','");
		stringBuffer.append(user.getRemark());
		stringBuffer.append("')");
		try {
			System.out.println(stringBuffer.toString());
			pstmt = (Statement) connection.createStatement();
			pstmt.executeUpdate(stringBuffer.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	public void deleteUser(Serializable id) {
		Connection connection = DBUtils.getConnection() ;
		StringBuffer buffer = new StringBuffer() ;
		buffer.append("delete from s_user where userID="+id) ;
		Statement pstmt = null ;
		try {
			pstmt = (Statement) connection.createStatement();
			pstmt.executeUpdate(buffer.toString()) ;
		} catch (Exception e) {
			e.printStackTrace() ;
		}
	}

	public void updateUser(User user) {
		
	} 

	public User getUserByUsernameAndPassword(String username, String password) {
		Connection connection = DBUtils.getConnection() ;
		StringBuffer buffer = new StringBuffer() ;
		buffer.append("select * from s_user where username='" + username +"' and password='" + password + "'") ;
		Statement pstmt = null ;
		ResultSet rs = null ;
		User user = new User() ;
		try {
			pstmt = (Statement) connection.createStatement() ;
			rs = pstmt.executeQuery(buffer.toString()) ;
			if(rs.next()) {
				user.setUserID(rs.getInt("userID")) ;
				user.setUsername(rs.getString("username")) ;
				user.setPassword(rs.getString("password")) ;
				user.setSex(rs.getString("sex")) ;
				user.setBirthday(rs.getString("birthday")) ;
				user.setEducation(rs.getString("education")) ;
				user.setTelephone(rs.getString("telephone")) ;
				user.setInterest(rs.getString("interest")) ;
				user.setPath(rs.getString("path")) ;
				user.setFilename(rs.getString("filename")) ;
				user.setRemark(rs.getString("remark")) ;
				return user ;
			}  else {
				return null ;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null ;
	}

}
